<?php

namespace App\Exports;

use App\Models\AgentWithdrawRecord;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Carbon;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DataType;

class WithdrawRecordExport extends BaseExport implements WithCustomValueBinder, FromQuery
{
    public function headings(): array
    {
        return [
            '代理手机号',
            '代理名称',
            '身份证',
            '银行卡名称',
            '持卡人姓名',
            '银行卡号',
            '联行号',
            '预留手机号',
            '提现金额',
            '申请时间',
            '状态',
            '备注'
        ];
    }

    public function map($row): array
    {
        $row = $row->toArray();

        return [
            $row['agent']['phone'],
            $this->removeUncommonWords($this->removeEmoji($row['agent']['name'])),
            $row['cardholder_id_number'],
            $row['bank_name'],
            $row['cardholder_name'],
            $row['bank_card_account'],
            $this->getCnapsCode($row['bank_name']),
            $row['bank_phone'],
            number_format($row['withdrawal_amount'] / 100, 2),
            $row['created_at'],
            AgentWithdrawRecord::STATUS_MAP[$row['status']],
            $row['audit_remarks'],
        ];
    }

    /**
     * @return \Illuminate\Database\Query\Builder
     */
    public function query()
    {
        return AgentWithdrawRecord::query()
            ->with(['agent'])
            ->when(($this->request['phone'] ?? '') || ($this->request['name'] ?? ''), function (Builder $builder) {
                $builder
                    ->join('agents', 'agent_withdraw_records.agent_id', '=', 'agents.id')
                    ->when($this->request['phone'] ?? '', function (Builder $builder, $phone) {
                        $builder->where('agents.phone', 'like', "$phone%");
                    })
                    ->when($this->request['name'], function (Builder $builder, $name) {
                        $builder->where('agents.name', 'like', "$name%");
                    });
            })
            ->when($this->request['created_at_begin'] ?? '', function (Builder $builder, $createdAtBegin) {
                $builder->where('agent_withdraw_records.created_at', '>=', $createdAtBegin);
            })
            ->when($this->request['created_at_end'] ?? '', function (Builder $builder, $createdAtEnd) {
                $builder->where('agent_withdraw_records.created_at', '<=', Carbon::parse($createdAtEnd)->endOfDay());
            })
            ->select(['agent_withdraw_records.*']);
    }

    public function bindValue(Cell $cell, $value)
    {
        if (is_numeric($value) &&
            $cell->getColumn() !== 'I') { // 金额不去除科学计数法

            $cell->setValueExplicit($value, DataType::TYPE_STRING);

            return true;
        }

        // else return default behavior
        return parent::bindValue($cell, $value);
    }

    //根据银行名称获取银行联号
    public function getCnapsCode($bank_name){
        $codeList = [
            '国家开发银行' => '201100000017',
            '中国工商银行' => '102100099996',
            '中国农业银行' => '103100000026',
            '中国银行' => '104100000004',
            '中国建设银行' => '105100000017',
            '中国邮政储蓄银行' => '403100000004',
            '交通银行' => '301290000007',
            '招商银行' => '308584000013',
            '上海浦东发展银行' => '310290000013',
            '兴业银行' => '309391000011',
            '华夏银行' => '304100040000',
            '广东发展银行' => '306581000003',
            '中国民生银行' => '305100000013',
            '中信银行' => '302100011000',
            '中国光大银行' => '303100000006',
            '恒丰银行' => '315456000105',
            '浙商银行' => '316331000018',
            '渤海银行' => '318110000014',
            '平安银行' => '307584007998',
            '上海农村商业银行' => '322290000011',
            '玉溪市商业银行' => '',
            '尧都农商行' => '',
            '北京银行' => '313100000013',
            '上海银行' => '325290000012',
            '江苏银行' => '313301099999',
            '杭州银行' => '313331000014',
            '南京银行' => '313301008887',
            '宁波银行' => '313332082914',
            '徽商银行' => '319361000013',
            '长沙银行' => '313551088886',
            '成都银行' => '313651099999',
            '重庆银行' => '313653000013',
            '大连银行' => '313222080002',
            '南昌银行' => '313421087506',
            '福建海峡银行' => '313391080007',
            '汉口银行' => '313521000011',
            '温州银行' => '313333007331',
            '青岛银行' => '313452060150',
            '台州银行' => '313345001665',
            '嘉兴银行' => '313335081005',
            '常熟农村商业银行' => '314305506621',
            '南海农村信用联社' => '',
            '常州农村信用联社' => '',
            '内蒙古银行' => '313191000011',
            '绍兴银行' => '313337009004',
            '顺德农商银行' => '314588000016',
            '吴江农商银行' => '314305400015',
            '齐商银行' => '313453001017',
            '贵阳市商业银行' => '313701098010',
            '遵义市商业银行' => '',
            '湖州市商业银行' => '313336071575',
            '龙江银行' => '313261099913',
            '晋城银行JCBANK' => '313168000003',
            '浙江泰隆商业银行' => '313345010019',
            '广东省农村信用社联合社' => '402581090008',
            '东莞农村商业银行' => '402602000018',
            '浙江民泰商业银行' => '313345400010',
            '广州银行' => '313581003284',
            '辽阳市商业银行' => '313231000013',
            '江苏省农村信用联合社' => '402301099998',
            '廊坊银行' => '313146000019',
            '浙江稠州商业银行' => '313338707013',
            '德阳商业银行' => '',
            '晋中市商业银行' => '313175000011',
            '苏州银行' => '313305066661',
            '桂林银行' => '313617000018',
            '乌鲁木齐市商业银行' => '313881000002',
            '成都农商银行' => '314651000000',
            '张家港农村商业银行' => '314305670002',
            '东莞银行' => '313602088017',
            '莱商银行' => '313463400019',
            '北京农村商业银行' => '402100000018',
            '天津农商银行' => '317110010019',
            '上饶银行' => '313433076801',
            '富滇银行' => '313731010015',
            '重庆农村商业银行' => '314653000011',
            '鞍山银行' => '313223007007',
            '宁夏银行' => '313871000007',
            '河北银行' => '313121006888',
            '华融湘江银行' => '313551070008',
            '自贡市商业银行' => '313655091983',
            '云南省农村信用社' => '402731057238',
            '吉林银行' => '313241066661',
            '东营市商业银行' => '313455000018',
            '昆仑银行' => '313882000012',
            '鄂尔多斯银行' => '313205057830',
            '邢台银行' => '313131000016',
            '晋商银行' => '313161000017',
            '天津银行' => '313110000017',
            '营口银行' => '313228000276',
            '吉林农信' => '402241000015',
            '山东农信' => '402451000010',
            '西安银行' => '313791000015',
            '河北省农村信用社' => '402121000009',
            '宁夏黄河农村商业银行' => '',
            '贵州省农村信用社' => '402701002999',
            '阜新银行' => '313229000008',
            '湖北银行黄石分行' => '313521006000',
            '浙江省农村信用社联合社' => '402331000007',
            '新乡银行' => '313491099996',
            '湖北银行宜昌分行' => '313521006000',
            '乐山市商业银行' => '313665092924',
            '江苏太仓农村商业银行' => '314305106644',
            '驻马店银行' => '313491099996',
            '赣州银行' => '313428076517',
            '无锡农村商业银行' => '314302066666',
            '广西北部湾银行' => '313611001018',
            '广州农商银行' => '314581000011',
            '江苏江阴农村商业银行' => '314302200018',
            '平顶山银行' => '313495081900',
            '泰安市商业银行' => '313463000993',
            '南充市商业银行' => '313673093259',
            '重庆三峡银行' => '321667090019',
            '中山小榄村镇银行' => '',
            '邯郸银行' => '313127000013',
            '库尔勒市商业银行' => '313888000013',
            '锦州银行' => '313227000012',
            '齐鲁银行' => '313451000019',
            '青海银行' => '313851000018',
            '阳泉银行' => '313163000004',
            '盛京银行' => '313221030008',
            '抚顺银行' => '313224000015',
            '郑州银行' => '313491000232',
            '深圳农村商业银行' => '402584009991',
            '潍坊银行' => '313458000013',
            '九江银行' => '313424076706',
            '江西省农村信用' => '402421099990',
            '河南省农村信用' => '402491000026',
            '甘肃省农村信用' => '402821000015',
            '四川省农村信用' => '402651020006',
            '广西省农村信用' => '402611099974',
            '陕西信合' => '402791000010',
            '武汉农村商业银行' => '402521090019',
            '宜宾市商业银行' => '313671000017',
            '昆山农村商业银行' => '314305206650',
            '石嘴山银行' => '313872097457',
            '衡水银行' => '313148053964',
            '信阳银行' => '313491099996',
            '鄞州银行' => '402332010004',
            '张家口市商业银行' => '313138000019',
            '许昌银行' => '313491099996',
            '济宁银行' => '313461000012',
            '开封市商业银行' => '313491099996',
            '威海市商业银行' => '313465000010',
            '湖北银行' => '313521006000',
            '承德银行' => '313141052422',
            '丹东银行' => '313226009000',
            '金华银行' => '313338009688',
            '朝阳银行' => '313234001089',
            '临商银行' => '313473070018',
            '包商银行' => '313192000013',
            '兰州银行' => '313821001016',
            '周口银行' => '313491099996',
            '德州银行' => '313468000015',
            '三门峡银行' => '313491099996',
            '安阳银行' => '313491099996',
            '安徽省农村信用社' => '402361018886',
            '湖北省农村信用社' => '402521000032',
            '湖南省农村信用社' => '402551080008',
            '广东南粤银行' => '313591001001',
            '洛阳银行' => '313493080539',
            '农信银清算中心' => '',
            '城市商业银行资金清算中心' => '',
        ];
        return isset($codeList[$bank_name]) ? $codeList[$bank_name] : '';

    }
}
